﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using Itegracion.Framework.Forms;
using Microsoft.Practices.EnterpriseLibrary.Data;
using System.Data.Common;
using System.Data;

namespace Itegracion.iAlmacen.Model
{
    public class Producto
    {
        public static List<Element> TraerListado(List<Element> entrada)
        {
            List<Element> rpta = new List<Element>();

            Database db = DatabaseFactory.CreateDatabase("dbItegracion");
            DbCommand command = db.GetSqlStringCommand(@"
                SELECT
                  p.id, p.codigo, p.nombre, p.idMarca, m.nombre as marca, p.idFamilia, c.nombre as familia
                FROM
                  Producto p
                  JOIN Constante c ON c.codigo = 'Familia' AND p.idFamilia = c.oid
                  JOIN Constante m ON m.codigo = 'Marca' AND p.idMarca = m.oid
                ORDER BY
                  p.id DESC");
            DataTable dt = db.ExecuteDataSet(command).Tables[0];
            rpta.Add(new Element("rpta", dt));

            return rpta;
        }
        public static List<Element> Insertar(List<Element> entrada)
        {
            List<Element> rpta = new List<Element>();

            Database db = DatabaseFactory.CreateDatabase("dbItegracion");
            DbCommand command = db.GetSqlStringCommand(@"
                INSERT INTO Producto(codigo, nombre, idMarca, idFamilia) VALUES(@codigo, @nombre, @idMarca, @idFamilia)
                SELECT @@IDENTITY
            ");
            db.AddInParameter(command, "codigo", DbType.String, entrada[1].Value);
            db.AddInParameter(command, "nombre", DbType.String, entrada[2].Value);
            db.AddInParameter(command, "idMarca", DbType.Int32, entrada[3].Value);
            db.AddInParameter(command, "idFamilia", DbType.Int32, entrada[4].Value);
            object id = db.ExecuteScalar(command);
            
            foreach (DataRow dr in (entrada[5].Value as DataTable).Rows)
            {
                command = db.GetSqlStringCommand(@"
                    INSERT INTO Equivalencia(idProducto, cantidad, idUnidad) VALUES(@idProducto, @cantidad, @idUnidad)
                ");
                db.AddInParameter(command, "idProducto", DbType.Int32, id);
                db.AddInParameter(command, "cantidad", DbType.Decimal, dr["cantidad"]);
                db.AddInParameter(command, "idUnidad", DbType.Int32, dr["idUnidad"]);
                db.ExecuteNonQuery(command);
            }
            foreach (DataRow dr in (entrada[6].Value as DataTable).Rows)
            {
                command = db.GetSqlStringCommand(@"
                    INSERT INTO Precio(idProducto, idUnidad, p1, p2, p3, p4, p5, p6) VALUES(@idProducto, @idUnidad, @p1, @p2, @p3, @p4, @p5, @p6)
                ");
                db.AddInParameter(command, "idProducto", DbType.Int32, id);
                db.AddInParameter(command, "idUnidad", DbType.Int32, dr["idUnidad"]);
                db.AddInParameter(command, "p1", DbType.Decimal, dr["p1"]);
                db.AddInParameter(command, "p2", DbType.Decimal, dr["p2"]);
                db.AddInParameter(command, "p3", DbType.Decimal, dr["p3"]);
                db.AddInParameter(command, "p4", DbType.Decimal, dr["p4"]);
                db.AddInParameter(command, "p5", DbType.Decimal, dr["p5"]);
                db.AddInParameter(command, "p6", DbType.Decimal, dr["p6"]);
                db.ExecuteNonQuery(command);
            }
            rpta.Add(new Element("rpta", "OK"));

            return rpta;
        }
        public static List<Element> Modificar(List<Element> entrada)
        {
            List<Element> rpta = new List<Element>();

            Database db = DatabaseFactory.CreateDatabase("dbItegracion");
            DbCommand command = db.GetSqlStringCommand(@"
                UPDATE Producto SET codigo=@codigo, nombre=@nombre, idMarca=@idMarca, idFamilia=@idFamilia WHERE id = @id
                    DELETE Equivalencia WHERE idProducto = @id
                    DELETE Precio WHERE idProducto = @id
                    DELETE Existencia WHERE idProducto = @id
            ");
            db.AddInParameter(command, "id", DbType.String, entrada[0].Value);
            db.AddInParameter(command, "codigo", DbType.String, entrada[1].Value);
            db.AddInParameter(command, "nombre", DbType.String, entrada[2].Value);
            db.AddInParameter(command, "idMarca", DbType.String, entrada[3].Value);
            db.AddInParameter(command, "idFamilia", DbType.Int32, entrada[4].Value);
            db.ExecuteNonQuery(command);

            foreach (DataRow dr in (entrada[5].Value as DataTable).Rows)
            {
                command = db.GetSqlStringCommand(@"
                    INSERT INTO Equivalencia(idProducto, cantidad, idUnidad) VALUES(@idProducto, @cantidad, @idUnidad)
                ");
                db.AddInParameter(command, "idProducto", DbType.Int32, entrada[0].Value);
                db.AddInParameter(command, "cantidad", DbType.Decimal, dr["cantidad"]);
                db.AddInParameter(command, "idUnidad", DbType.Int32, dr["idUnidad"]);
                db.ExecuteNonQuery(command);
            }
            foreach (DataRow dr in (entrada[6].Value as DataTable).Rows)
            {
                command = db.GetSqlStringCommand(@"
                    INSERT INTO Precio(idProducto, idUnidad, p1, p2, p3, p4, p5, p6) VALUES(@idProducto, @idUnidad, @p1, @p2, @p3, @p4, @p5, @p6)
                ");
                db.AddInParameter(command, "idProducto", DbType.Int32, entrada[0].Value);
                db.AddInParameter(command, "idUnidad", DbType.Int32, dr["idUnidad"]);
                db.AddInParameter(command, "p1", DbType.Decimal, dr["p1"]);
                db.AddInParameter(command, "p2", DbType.Decimal, dr["p2"]);
                db.AddInParameter(command, "p3", DbType.Decimal, dr["p3"]);
                db.AddInParameter(command, "p4", DbType.Decimal, dr["p4"]);
                db.AddInParameter(command, "p5", DbType.Decimal, dr["p5"]);
                db.AddInParameter(command, "p6", DbType.Decimal, dr["p6"]);
                db.ExecuteNonQuery(command);
            }
            foreach (DataRow dr in (entrada[7].Value as DataTable).Rows)
            {
                command = db.GetSqlStringCommand(@"
                    INSERT INTO Existencia(idProducto, idAlmacen, idUnidad, cantidad, precioUnitario, vencimiento, nroLote) 
                        VALUES(@idProducto, @idAlmacen, @idUnidad, @cantidad, @precioUnitario, @vencimiento, @nroLote)
                ");
                db.AddInParameter(command, "idProducto", DbType.Int32, entrada[0].Value);
                db.AddInParameter(command, "idAlmacen", DbType.Int32, dr["idAlmacen"]);
                db.AddInParameter(command, "idUnidad", DbType.Int32, dr["idUnidad"]);
                db.AddInParameter(command, "cantidad", DbType.Decimal, dr["cantidad"]);
                db.AddInParameter(command, "precioUnitario", DbType.Decimal, dr["precioUnitario"]);
                db.AddInParameter(command, "vencimiento", DbType.String, dr["vencimiento"]);
                db.AddInParameter(command, "nroLote", DbType.String, dr["nroLote"]);
                db.ExecuteNonQuery(command);
            }
            rpta.Add(new Element("rpta", "OK"));

            return rpta;
        }
        public static List<Element> Traer(List<Element> entrada)
        {
            List<Element> rpta = new List<Element>();

            Database db = DatabaseFactory.CreateDatabase("dbItegracion");
            DbCommand command = db.GetSqlStringCommand(@"
                SELECT p.id, p.codigo, p.nombre, p.idMarca, p.idFamilia
                    FROM Producto p WHERE p.id = @id
                SELECT idProducto, cantidad, idUnidad 
	                FROM Equivalencia WHERE idProducto = @id
                SELECT idProducto, idUnidad, p1, p2, p3, p4, p5, p6 
	                FROM Precio WHERE idProducto = @id
                SELECT idProducto, idAlmacen, idUnidad, cantidad, precioUnitario, vencimiento, nroLote 
	                FROM Existencia WHERE idProducto = @id
            ");
            db.AddInParameter(command, "id", DbType.Int32, entrada[0].Value);
            DataSet ds = db.ExecuteDataSet(command);
            rpta.AddRange(ds.Tables[0].Rows.ToList());
            rpta.Add(new Element("unidad", ds.Tables[1]));
            rpta.Add(new Element("precios", ds.Tables[2]));
            rpta.Add(new Element("existencias", ds.Tables[3]));

            return rpta;
        }
        public static List<Element> Navegar(List<Element> entrada)
        {
            List<Element> rpta = new List<Element>();

            Database db = DatabaseFactory.CreateDatabase("dbItegracion");
            DbCommand command = db.GetSqlStringCommand(@"
                SELECT idProducto, idUnidad, p1, p2, p3, p4, p5, p6 
	                FROM Precio WHERE idProducto = @id
                SELECT idProducto, idAlmacen, idUnidad, cantidad, precioUnitario, vencimiento, nroLote 
	                FROM Existencia WHERE idProducto = @id
            ");
            db.AddInParameter(command, "id", DbType.Int32, entrada[0].Value);
            DataSet ds = db.ExecuteDataSet(command);
            rpta.Add(new Element("precios", ds.Tables[0]));
            rpta.Add(new Element("existencias", ds.Tables[1]));

            return rpta;
        }
        public static List<Element> Nuevo(List<Element> entrada)
        {
            List<Element> rpta = new List<Element>();

            Database db = DatabaseFactory.CreateDatabase("dbItegracion");
            DbCommand command = db.GetSqlStringCommand(@"
                SELECT TOP 1 0 as idProducto, 1.00 as cantidad, 1 as idUnidad
	                FROM Equivalencia
                SELECT TOP 1 0 as idProducto, 1 as idUnidad, 0.00 as p1, 0.00 as p2, 0.00 as p3, 0.00 as p4, 0.00 as p5, 0.00 as p6 
	                FROM Precio
                SELECT TOP 0 0 as idProducto, idAlmacen, idUnidad, cantidad, precioUnitario, vencimiento, nroLote 
	                FROM Existencia
            ");
            DataSet ds = db.ExecuteDataSet(command);
            rpta.Add(new Element("unidad", ds.Tables[0]));
            rpta.Add(new Element("precios", ds.Tables[1]));
            rpta.Add(new Element("existencias", ds.Tables[2]));

            return rpta;
        }
        public static List<Element> Eliminar(List<Element> entrada)
        {
            List<Element> rpta = new List<Element>();

            Database db = DatabaseFactory.CreateDatabase("dbItegracion");
            DbCommand command = db.GetSqlStringCommand(@"
                DELETE FROM Producto WHERE id = @id");
            db.AddInParameter(command, "id", DbType.Int32, entrada[0].Value);
            db.ExecuteNonQuery(command);
            rpta.Add(new Element("rpta", "OK"));

            return rpta;
        }
        public static List<Element> TraerKardex(List<Element> entrada)
        {
            List<Element> rpta = new List<Element>();

            Database db = DatabaseFactory.CreateDatabase("dbItegracion");
            DbCommand command = db.GetSqlStringCommand(@"
                SELECT
	                d.id,
	                d.fecha, 
	                c.valor as documento,
	                d.serie, 
	                d.numero,
	                u.valor as unidad, 
	                CASE WHEN d.idTipo IN (1,6,7,8) THEN di.cantidad END as entrada,
	                CASE WHEN d.idTipo IN (2,3,4,5) THEN di.cantidad END as salida,
	                di.stock as saldo
                FROM
	                Documento d
	                JOIN Constante c ON d.idTipo = c.oid AND c.codigo = 'TipoDocumento'
	                JOIN DocumentoItem di ON di.idDocumento = d.id
	                JOIN Constante u ON di.idUnidad = u.oid AND u.codigo = 'Unidad'
                WHERE
	                d.idEstado <> 9
	                AND d.afectaStock = 1
	                AND di.idProducto = @id
                ORDER BY
	                d.fecha, d.id");
            db.AddInParameter(command, "id", DbType.Int32, entrada[0].Value);
            DataTable dt = db.ExecuteDataSet(command).Tables[0];
            rpta.Add(new Element("rpta", dt));

            return rpta;
        }
    }
}
